<?php
/**
 * Database Table abstract
 * 
 * @todo pretty join support
 */
class LtDbTableDataGateway
{
    public $configHandle;
    public $dbh;
    /**
     * The created field name
     * 
     * @var string 
     */
    public $createdColumn;
    /**
     * The modified field name
     * 
     * @var string 
     */
    public $modifiedColumn;
    /**
     * The table name
     * 
     * @var string 
     */
    public $tableName;
    /**
     * The fields array
     * 
     * @var array 
     */
    protected $fields;
    /**
     * The primary key
     * 
     * @var string 
     */
    protected $primaryKey;
    protected $servers;
    /**
     * Build table's field list
     * 
     * @return array 
     */
    protected function buildFieldList()
    {
        if (!empty($this->fields)) {
            return true;
        }
        $servers = $this->configHandle->get('db.servers');
        $group   = $this->dbh->group;
        $node    = $this->dbh->node;
        $role    = $this->dbh->role;
        $table   = $this->tableName;
        $host    = key($servers[$group][$node][$role]);
        $key     = md5($group . $node . $role . $table . $host . $table);
        if (!$value = $this->configHandle->get($key)) {
            $sql          = $this->dbh->sqlAdapter->showFields($this->tableName);
            $rs           = $this->dbh->query($sql);
            $this->fields = $this->dbh->sqlAdapter->getFields($rs);
            foreach ($this->fields as $field) {
                if ($field['primary'] == 1) {
                    $this->primaryKey = $field['name'];
                    break;
                }
            }
            $value['fields']     = $this->fields;
            $value['primaryKey'] = $this->primaryKey;
            $this->configHandle->addConfig(array(
                $key => $value
            ));
        } else {
            $this->fields     = $value['fields'];
            $this->primaryKey = $value['primaryKey'];
        }
    }
    /**
     * A shortcut to SELECT COUNT(*) FROM table WHERE condition
     * 
     * @param array $args 
     * @return integer 
     * @example count(array('expression' => 'id < :id', 'value' => array('id' => 10)));
     */
    public function count($args = null)
    {
        $selectTemplate = 'SELECT COUNT(*) AS total FROM %s%s';
        $where          = isset($args['where']['expression']) ? ' WHERE ' . $args['where']['expression'] : '';
        $bind           = isset($args['where']['value']) ? $args['where']['value'] : array();
        $join           = isset($args['join']) ? ' ' . $args['join'] : '';
        $sql            = sprintf($selectTemplate, $this->tableName, $join . $where);
        $queryResult    = $this->dbh->query($sql, $bind);
        return $queryResult[0]['total'];
    }
    /**
     * Delete a row by primary key
     * 
     * @param string $primaryKeyId 
     * @return string 
     * @example delete(10);
     */
    public function delete($primaryKeyId)
    {
        $this->buildFieldList();
        $where['expression']               = $this->primaryKey . '=:' . $this->primaryKey;
        $where['value'][$this->primaryKey] = $primaryKeyId;
        return $this->deleteRows($where);
    }
    /**
     * Delete many rows from table
     * Please use this method carefully!
     * 
     * @param array $args 
     * @return integer 
     * @example deleteRows(array('expression' => "id > :id", 'value' => array('id' => 2)));
     */
    public function deleteRows($args = null)
    {
        $deleteTemplate = 'DELETE FROM %s%s';
        $where          = isset($args['expression']) ? ' WHERE ' . $args['expression'] : '';
        $bind           = isset($args['value']) ? $args['value'] : array();
        $sql            = sprintf($deleteTemplate, $this->tableName, $where);
        return $this->dbh->query($sql, $bind);
    }
    /**
     * Fetch one row from table by primary key
     * 
     * @param string $primaryKeyId 
     * @param array $args 
     * @param boolean $useSlave 
     * @return array 
     * @example fetch(10)
     */
    public function fetch($primaryKeyId, $args = null, $useSlave = true)
    {
        $this->buildFieldList();
        $fetchRowsArgs['where']['expression']               = $this->tableName . '.' . $this->primaryKey . '=:' . $this->primaryKey;
        $fetchRowsArgs['where']['value'][$this->primaryKey] = $primaryKeyId;
        $fetchRowsArgs['fields']                            = isset($args['fields']) ? $args['fields'] : null;
        $fetchRowsArgs['join']                              = isset($args['join']) ? $args['join'] : null;
        $fetchResult                                        = $this->fetchRows($fetchRowsArgs, $useSlave);
        return $fetchResult ? $fetchResult[0] : $fetchResult;
    }
    /**
     * Fetch many rows from table
     * 
     * @param array $args 
     * @param boolean $useSlave 
     * @return array 
     * @example fetchRows(array('where' => array('expression' => "id > :id", 'value' => array('id' => 2))));
     */
    public function fetchRows($args = null, $useSlave = true)
    {
        $this->buildFieldList();
        $selectTemplate = 'SELECT %s FROM %s%s';
        $fields         = isset($args['fields']) ? $args['fields'] : '*';
        $where          = isset($args['where']['expression']) ? ' WHERE ' . $args['where']['expression'] : '';
        $bind           = isset($args['where']['value']) ? $args['where']['value'] : array();
        $join           = isset($args['join']) ? ' ' . $args['join'] : '';
        $orderby        = isset($args['orderby']) ? ' ORDER BY ' . $args['orderby'] : '';
        $groupby        = isset($args['groupby']) ? ' GROUP BY ' . $args['groupby'] : '';
        $sql            = sprintf($selectTemplate, $fields, $this->tableName, $join . $where . $groupby . $orderby);
        if (isset($args['limit'])) {
            $offset = isset($args['offset']) ? $args['offset'] : 0;
            $sql    = $sql . ' ' . $this->dbh->sqlAdapter->limit($args['limit'], $offset);
        }
        return $this->dbh->query($sql, $bind);
    }
    /**
     * Insert one row into table, then return the inserted row's pk
     * 
     * @param array $args 
     * @return string 
     * @example insert(array('name' => 'lily', 'age' => '12'));
     */
    public function insert($args = null)
    {
        $this->buildFieldList();
        $insertTemplate = 'INSERT INTO %s (%s) VALUES (%s)';
        $fields         = array();
        $placeHolders   = array();
        foreach ($args as $field => $value) {
            if (isset($this->fields[$field])) {
                $fields[]       = $field;
                $placeholders[] = ":$field";
                $values[$field] = $value;
            }
        }
        if (isset($this->fields[$this->createdColumn]) && !isset($args[$this->createdColumn])) {
            $fields[]                     = $this->createdColumn;
            $placeholders[]               = ':' . $this->createdColumn;
            $values[$this->createdColumn] = time();
        }
        if (isset($this->fields[$this->modifiedColumn]) && !isset($args[$this->modifiedColumn])) {
            $fields[]                      = $this->modifiedColumn;
            $placeholders[]                = ':' . $this->modifiedColumn;
            $values[$this->modifiedColumn] = time();
        }
        $sql         = sprintf($insertTemplate, $this->tableName, implode(",", $fields), implode(",", $placeholders));
        $bind        = $values;
        $queryResult = $this->dbh->query($sql, $bind);
        return isset($args[$this->primaryKey]) ? $args[$this->primaryKey] : $queryResult;
    }
    /**
     * Update one row  by primary key
     * 
     * @param string $primaryKeyId 
     * @param array $args 
     * @return integer 
     * @example update(1, array('name' => 'lily', 'age' => '18'));
     */
    public function update($primaryKeyId, $args = null)
    {
        $this->buildFieldList();
        $where['expression']               = $this->primaryKey . '=:' . $this->primaryKey;
        $where['value'][$this->primaryKey] = $primaryKeyId;
        return $this->updateRows($where, $args);
    }
    /**
     * Update manay rows
     * Please use this method carefully!
     * 
     * @param array $where 
     * @param array $args 
     * @return integer 
     * @example updateRows(array('expression' => "id > :id", 'value' => array('id' => 2)), array('name' => 'kiwi', 'age' => '1'));
     */
    public function updateRows($where, $args = null)
    {
        $this->buildFieldList();
        $updateTemplate   = 'UPDATE %s SET %s%s';
        $fields           = array();
        $bindParameters   = array();
        $placeholderStyle = isset($where['value']) && array_key_exists(0, $where['value']) ? 'questionMark' : 'named';
        foreach ($args as $field => $value) {
            if (isset($this->fields[$field])) {
                if ($args[$field] instanceof DbExpression) {
                    $fields[] = "$field=" . $args[$field]->__toString();
                } else {
                    if ('named' == $placeholderStyle) {
                        $fields[]               = "$field=:$field";
                        $bindParameters[$field] = $args[$field];
                    } else {
                        $fields[]         = "$field=?";
                        $bindParameters[] = $args[$field];
                    }
                }
            }
        }
        if (isset($this->fields[$this->modifiedColumn]) && !isset($args[$this->modifiedColumn])) {
            if ('named' == $placeholderStyle) {
                $fields[]                              = $this->modifiedColumn . '=:' . $this->modifiedColumn;
                $bindParameters[$this->modifiedColumn] = time();
            } else {
                $fields[]         = $this->modifiedColumn . '=?';
                $bindParameters[] = time();
            }
        }
        $whereCause = isset($where['expression']) ? ' WHERE ' . $where['expression'] : '';
        $bind       = isset($where['value']) ? array_merge($bindParameters, $where['value']) : $bindParameters;
        $sql        = sprintf($updateTemplate, $this->tableName, implode(",", $fields), $whereCause);
        return $this->dbh->query($sql, $bind);
    }
}
